package com.directions.android.storage.sqlite;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;

import com.directions.android.storage.Location;

public class LocationSQLiteHelper extends SQLiteOpenHelper {
	private static final int VERSION = 3;

	private static final String NAME = "Locations";
	private static final String LATITUDE = "latitude";
	private static final String LONGITUDE = "longitude";
	private static final String TITLE = "title";

	private static final String TABLE_NAME = NAME + " (" + BaseColumns._ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, " + LATITUDE + " REAL, "
			+ LONGITUDE + " REAL, " + TITLE + " TEXT)";

	public static final String RAW_QUERY = "SELECT " + BaseColumns._ID
			+ ", latitude, longitude, title FROM " + NAME;

	private static final String SQL_CREATE = "CREATE TABLE IF NOT EXISTS "
			+ TABLE_NAME;

	public LocationSQLiteHelper(Context contexto) {
		super(contexto, "DB" + NAME, null, VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// Se ejecuta la sentencia SQL de creaci�n de la tabla
		db.execSQL(SQL_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int versionAnterior,
			int versionNueva) {
		// NOTE: in order to keep it simple we just drop the contents of the old
		// table
		// and create a new one
		db.execSQL("DROP TABLE IF EXISTS " + NAME);

		// We create the new table
		db.execSQL(SQL_CREATE);
	}

	/**
	 * If location's id is -1, a row with a primary key auto incremented will be
	 * inserted.
	 * 
	 * @param location
	 */
	public void insert(Location location) {

		ContentValues val = new ContentValues();
		int pk = location.getId();
		if (pk != -1) {
			val.put(BaseColumns._ID, pk);
		}
		val.put(LATITUDE, location.getLatitude());
		val.put(LONGITUDE, location.getLongitude());
		val.put(TITLE, location.getTitle());

		SQLiteDatabase db = getWritableDatabase();
		Long id;
		try{
			id = db.insertOrThrow(NAME, BaseColumns._ID, val);			
		} catch (SQLException ex) {
			id = -1L;
		}
		if(id != -1){
			location.setId(id.intValue());
		}
		db.close();
	}

	public void updateRow(Location location) {
		int locId = location.getId();

		if (locId == -1)
			throw new UnsupportedOperationException(
					"In order to update a row a valid id must be provided.");

		String whereClause = BaseColumns._ID + "=?";
		String[] whereArgs = new String[] { String.valueOf(locId) };

		ContentValues args = new ContentValues();
		args.put(TITLE, location.getTitle());
		args.put(LATITUDE, location.getLatitude());
		args.put(LONGITUDE, location.getLongitude());

		SQLiteDatabase db = this.getWritableDatabase();
		db.update(NAME, args, whereClause, whereArgs);
		db.close();
	}

	public void delete(Location location) {

		String whereClause = BaseColumns._ID + "=?";
		String[] whereArgs = new String[] { String.valueOf(location.getId()) };

		SQLiteDatabase db = getWritableDatabase();
		db.delete(NAME, whereClause, whereArgs);
		db.close();
	}

	public List<Location> getList(Context ctx) throws Exception {
		return getList(ctx, new ArrayList<Location>());
	}

	public List<Location> getList(Context ctx, List<Location> places)
			throws Exception {

		// Obtain the data drom the DB
		SQLiteDatabase db = getWritableDatabase();

		Cursor cursor = db.rawQuery(RAW_QUERY, null);
		if (cursor.moveToFirst()) {
			do {
				int id = cursor.getInt(0);
				double latitude = cursor.getDouble(1);
				double longitude = cursor.getDouble(2);
				String title = cursor.getString(3);
				Location res = new Location(id, latitude, longitude, title);
				places.add(res);

			} while (cursor.moveToNext());
		}
		cursor.close();
		db.close();
		return places;
	}
}